import pymysql
from config.settings import DB_HOST, DB_USER ,DB_PASSWORD,DB_NAME,DB_PORT
# 1. 连接数据库
def connect_mysql():
    connection = pymysql.connect(
        host=DB_HOST,  # 数据库地址
        user=DB_USER, # 用户名
        password= DB_PASSWORD,  # 密码
        database=DB_NAME,  # 数据库名
        charset='utf8',  # 字符集
        port=DB_PORT,
        cursorclass=pymysql.cursors.DictCursor  # 结果以字典形式返回
    )
    return connection

# DB_CONFIG = {
#     'host': '192.168.248.133',
#     'user': 'flask',
#     'password': '@0713NBwq',
#     'database': 'sqjxflask',
#     'charset': 'utf8',
#     'port': 3308,
#     'cursorclass': pymysql.cursors.DictCursor
# }
# try:
#     # # 2. 创建游标对象
#     # with connection.cursor() as cursor:
#     #     # 3. 执行 SQL 命令
#     #     # 示例 1：创建表
#     #     create_table_sql = """
#     #                        CREATE TABLE IF NOT EXISTS users \
#     #                        ( \
#     #                            id \
#     #                            INT \
#     #                            AUTO_INCREMENT \
#     #                            PRIMARY \
#     #                            KEY, \
#     #                            name \
#     #                            VARCHAR \
#     #                        ( \
#     #                            50 \
#     #                        ) NOT NULL,
#     #                            email VARCHAR \
#     #                        ( \
#     #                            100 \
#     #                        ) NOT NULL
#     #                            ) \
#     #                        """
#     #     cursor.execute(create_table_sql)
#     #
#     #     # 示例 2：插入数据
#     #     insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
#     #     cursor.execute(insert_sql, ('Alice', 'alice@example.com'))
#     #     cursor.execute(insert_sql, ('Bob', 'bob@test.org'))
#     #
#     #     # 提交事务（重要！）
#     #     connection.commit()
#
#     # 4. 查询数据
#     with connection.cursor() as cursor:
#         select_sql = "SELECT * FROM student"
#         cursor.execute(select_sql)
#         result = cursor.fetchall()  # 获取所有记录
#         print("查询结果:")
#         for row in result:
#             print(row)  # 输出：{'id': 1, 'name': 'Alice', 'email': 'alice@example.com'}...
#
#
# #     # 5. 更新数据
# #     with connection.cursor() as cursor:
# #         update_sql = "UPDATE users SET email=%s WHERE name=%s"
# #         cursor.execute(update_sql, ('bob@new.com', 'Bob'))
# #         connection.commit()  # 提交
# #
# #     # 6. 删除数据
# #     with connection.cursor() as cursor:
# #         delete_sql = "DELETE FROM users WHERE name=%s"
# #         cursor.execute(delete_sql, ('Alice',))
# #         connection.commit()
# #
# except Exception as e:
#     print(f"操作出错: {e}")
#     connection.rollback()  # 出错时回滚
#
# finally:
#     # 7. 关闭连接
#     connection.close()
#
# # import pymysql
# #
# # def get_connection():
# #     """获取数据库连接"""
# #     try:
# #         connection = pymysql.connect(
# #             host='192.168.248.133',
# #             user='flask',
# #             password='@0713NBwq',
# #             database='sqjxflask',
# #             charset='utf8',
# #             port=3308,
# #             cursorclass=pymysql.cursors.DictCursor
# #         )
# #         return connection
# #     except Exception as e:
# #         print(f"数据库连接失败: {e}")
# #         return None
# #
# # # 可以保留测试代码，但注释掉连接关闭部分
# # if __name__ == "__main__":
# #     conn = get_connection()
# #     if conn:
# #         try:
# #             with conn.cursor() as cursor:
# #                 select_sql = "SELECT * FROM student"
# #                 cursor.execute(select_sql)
# #                 result = cursor.fetchall()
# #                 print("查询结果:")
# #                 for row in result:
# #                     print(row)
# #         except Exception as e:
# #             print(f"操作出错: {e}")
# #         finally:
# #             conn.close()
